package com.useeinfo.oa.modules.maintaining.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.useeinfo.framework.extend.dao.CrudDao;
import com.useeinfo.framework.sugar.data.QueryParam;
import com.useeinfo.framework.sugar.data.QueryUtils;
import com.useeinfo.framework.sugar.tools.DateTimeUtils;
import com.useeinfo.framework.sugar.tools.StringConverters;
import com.useeinfo.oa.common.utils.ResultUtil.ResultDto;
import com.useeinfo.oa.common.utils.ResultUtil.ResultUtil;
import com.useeinfo.oa.modules.base.entity.Enterprise;
import com.useeinfo.oa.modules.base.entity.Organization;
import com.useeinfo.oa.modules.base.entity.Position;
import com.useeinfo.oa.modules.base.entity.SystemUserOrganization;
import com.useeinfo.oa.modules.maintaining.entity.AuditOrder;
import com.useeinfo.oa.modules.maintaining.entity.MaintainOrder;
import com.useeinfo.oa.modules.maintaining.enums.OrderAssignTypeEnum;
import com.useeinfo.oa.modules.maintaining.enums.OrderStatusEnum;
import com.useeinfo.oa.modules.repair.entity.RepairOrder;
import com.useeinfo.oa.modules.security.biz.OrganizationBiz;
import com.useeinfo.oa.modules.security.entity.SystemUser;
import javafx.geometry.Pos;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.util.*;

/**
 * @Auther: xudong.sun
 * @Date: 2018/12/28 17:21
 * @Description:
 */
@Repository("maintainOrderDao")
public class MaintainOrderDao implements CrudDao<MaintainOrder> {

    private final static Logger logger = LoggerFactory.getLogger(MaintainOrderDao.class);

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private OrganizationBiz organizationBiz;

    public List<SystemUser> submitOrderSendMsg(Organization organization, Position position, SystemUser systemUser, Enterprise enterprise) {
        String jpql = "SELECT s FROM SystemUser s  WHERE 1 = 1 " +
                "AND s.deleteStatus = false " +
                "AND s.operationRole = 'common' " +
                " AND " + StringConverters.null2Long(enterprise.getId()) + " in elements(s.enterpriseSet) " +
                " AND exists (SELECT ss FROM s.systemUserOrganizationSet ss WHERE ss.organization.id = " + StringConverters.null2Long(organization.getId()) + " ";
        if (position != null) {
            jpql += "AND " + position.getId() + " in elements(s.positionSet) ";
        }
        if (systemUser != null) {
            jpql += "AND s.id = " + systemUser.getId() + " ";
        }
        TypedQuery<SystemUser> resultQuery = this.entityManager.createQuery(jpql, SystemUser.class);
        return resultQuery.getResultList();
    }


    /**
     * 年度计划
     *
     * @param id
     * @return
     */
    public List<Object> getYearPlan(String id) {
        String sql = "SELECT f.code,f.name,f.type,IFNULL(w.order_status_enum,''),IFNULL(a.apply_type_enum,'') as apply,IFNULL(month(w.start_date_time),'0'),a.reason FROM facility_maintain_snap o " +
                " LEFT JOIN facility f on o.facility_id=f.id and o.maintain_order_id is not null and o.temp_val = false LEFT JOIN maintain_order w on w.id=o.maintain_order_id and w.delete_status=true" +
                " LEFT JOIN apply_order a on a.maintain_order_id=o.maintain_order_id and a.effective=true and a.delete_status=true and o.facility_id=" + id;
        logger.info("sql语句为：" + sql);
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object> queryResultList = query.getResultList();
        return queryResultList;
    }

    //dashboard报表
    public String dashBoardInfo(String enterpriseId, String status, String date) {
        String dashHql = "select count(m.id) from MaintainOrder m where m.orderStatusEnum in (" + status + ")";
        if (StringUtils.isNotBlank(enterpriseId)) {
            dashHql = dashHql + " and m.enterprise.id=" + enterpriseId;
        }
        dashHql = dashHql + " and m.createDate like '" + date + "%'";
        dashHql = dashHql + " and m.deleteStatus=false";
//        logger.info("拼接完成的sql语句为：{}",dashHql);
        Query resultQuery = this.entityManager.createQuery(dashHql);
        Object o = resultQuery.getSingleResult();
        return o.toString();
    }

    //dashboard报表手机端
    public JSONArray getbetween(String enterpriseId, String start, String end, int type, boolean completed) {
        String dashHql = "select count(m.id),date_format(m.createDate,'%Y-%m-%d'),";//type 1小时统计，2天数统计
        if (type == 1) {
            dashHql = dashHql + "Hour(m.createDate)";
        } else {
            dashHql = dashHql + "Day(m.createDate)";
        }
        // 数据库中的数据格式是 2019-01-31 00:00:00  这里做一下日期时间拼接
        String sqlStart = start + " 00:00:00";
        String sqlEnd = end + " 23:59:59";
        dashHql = dashHql + "from MaintainOrder m  where m.createDate>='" + sqlStart + "' and m.createDate <'" + sqlEnd + "'";
        if (StringUtils.isNotBlank(enterpriseId) && !enterpriseId.equals("null")) {
            dashHql = dashHql + " and m.enterprise.id=" + enterpriseId;
        }
        if (completed) {
            dashHql = dashHql + " and m.orderStatusEnum = 'COMPLETED' ";
        }
        dashHql = dashHql + " and m.deleteStatus=false";
        if (type == 1) {
            dashHql = dashHql + " group by hour(m.createDate)";

        } else {
            dashHql = dashHql + " group by Day(m.createDate)";
        }
//        logger.info("拼接完成的sql语句为：{}", dashHql);
        Query query = this.entityManager.createQuery(dashHql);
        List<Object> o = query.getResultList();

        return organizationBiz.getDashBoardData(o, start, end, type);
    }

    //dashboard报表PC端,维护保养工单数量统计
    public String maintainReportNum(String enterpriseId, String status) { //status 1:当天，2前24小时，3，本月，4。已完成
        String dashHql = "select count(m.id) from maintain_order m where m.delete_status = false";
        if (StringUtils.isNotBlank(enterpriseId)) {
            dashHql = dashHql + " and m.enterprise_id =" + enterpriseId;
        }
        if (status.equals("1")) {
            dashHql = dashHql + " and m.create_date = to_days(now())";
        } else if ("2".equals(status)) {
            dashHql = dashHql + " and m.create_date >=  NOW() - INTERVAL 1 hour";
        } else if ("3".equals(status)) {
            dashHql = dashHql + " and DATE_FORMAT( m.create_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )";
        } else if ("4".equals(status)) {
            dashHql = dashHql + " and m.create_date = to_days(now()) and m.order_status_enum = 'COMPLETED'";
        }
//        logger.info("拼接完成的sql语句为：{}",dashHql);
        Query resultQuery = this.entityManager.createNativeQuery(dashHql);
        Object o = resultQuery.getSingleResult();
        return o.toString();
    }


    @Override
    public Map<String, Object> getSearchCondition(Map<String, String> queryHash) {
        Map<String, Object> conditionHash = new HashMap<>();
        conditionHash.put("s.deleteStatus = ?{paramIndex} ", false);
        if (queryHash == null || queryHash.size() == 0) {
            return conditionHash;
        }
        String enterpriseId = queryHash.get("enterpriseId");
        if (!StringUtils.isEmpty(enterpriseId)) {
            conditionHash.put("(s.enterprise.id = ?{paramIndex}) ", StringConverters.ToLong(enterpriseId));
        }
        String systemUserId = queryHash.get("systemUserId");
        if (StringUtils.isNotBlank(systemUserId)) {
            conditionHash.put("s.createUser.id = ?{paramIndex}", StringConverters.null2Long(systemUserId));
        }
        String receiveUserId = queryHash.get("receiveUserId");
        if (StringUtils.isNotBlank(receiveUserId)) {
            conditionHash.put("s.receiveUser.id = ?{paramIndex}", StringConverters.null2Long(receiveUserId));
        }
        String searchCode = queryHash.get("searchCode");
        if (!StringUtils.isEmpty(searchCode)) {
            conditionHash.put("(s.orderCode like ?{paramIndex}) ", "%" + searchCode + "%");
        }
        String searchName = queryHash.get("searchName");
        if (!StringUtils.isEmpty(searchName)) {
            conditionHash.put("(s.orderName like ?{paramIndex}) ", "%" + searchName + "%");
        }
        String searchOrderStatus = queryHash.get("searchOrderStatus");
        if (!StringUtils.isEmpty(searchOrderStatus)) {
            conditionHash.put("(s.orderStatusEnum = ?{paramIndex}) ", OrderStatusEnum.valueOf(searchOrderStatus));
        }
        String orderType = queryHash.get("orderType");
        if (!StringUtils.isEmpty(orderType)) {
            conditionHash.put("(s.orderType = ?{paramIndex}) ", Boolean.parseBoolean(orderType));
        }
        String createUserId = queryHash.get("createUserId");
        if (!StringUtils.isEmpty(createUserId)) {
            conditionHash.put("(s.createUser.id = ?{paramIndex}) ", StringConverters.null2Long(createUserId));
        }
        String idIn = queryHash.get("idIn");
        if (!StringUtils.isEmpty(idIn)) {
            conditionHash.put("(1 = ?{paramIndex} and s.id in (" + idIn + ")) ", 1);
        }
        String degree = queryHash.get("degree");
        if (!StringUtils.isEmpty(degree)) {
            conditionHash.put("(s.degree = ?{paramIndex}) ", degree);
        }
        String plannerId = queryHash.get("plannerId");
        if (!StringUtils.isEmpty(plannerId)) {
            conditionHash.put("(s.plannerOrder.id = ?{paramIndex}) ", StringConverters.null2Long(plannerId));
        }
        String audit = queryHash.get("audit");
        if (!StringUtils.isEmpty(audit)) {
            conditionHash.put("(s.orderStatusEnum = ?{paramIndex}) ", OrderStatusEnum.IN_AUDITING);
        }
        String auditIn = queryHash.get("auditIn");
        if (!StringUtils.isEmpty(auditIn)) {
            conditionHash.put("(s.orderStatusEnum = ?{paramIndex} or s.cancel = true or s.delay =true) ", OrderStatusEnum.IN_AUDITING);
        }
        String startTime = queryHash.get("startTime");
        if (!StringUtils.isEmpty(startTime)) {
            conditionHash.put("(s.startDateTime >= ?{paramIndex}) ", DateTimeUtils.parse("yyyy-MM-dd HH:mm", startTime));
        }
        String searchDate1 = queryHash.get("searchDate1");
        if (!StringUtils.isEmpty(searchDate1)) {
            conditionHash.put("(s.createDate >= ?{paramIndex}) ", DateTimeUtils.parse("yyyy-MM-dd HH:mm", searchDate1));
        }
        String searchDate2 = queryHash.get("searchDate2");
        if (!StringUtils.isEmpty(searchDate2)) {
            conditionHash.put("(s.createDate <= ?{paramIndex}) ", DateTimeUtils.parse("yyyy-MM-dd HH:mm", searchDate2));
        }
        String endTime = queryHash.get("endTime");
        if (!StringUtils.isEmpty(endTime)) {
            conditionHash.put("(s.endDateTime <= ?{paramIndex}) ", DateTimeUtils.parse("yyyy-MM-dd HH:mm", endTime));
        }

        String receiveUser = queryHash.get("receiveUser");
        if (!StringUtils.isEmpty(receiveUser)) {
            conditionHash.put("(s.receiveUser.id = ?{paramIndex}) ", StringConverters.null2Long(receiveUser));
        }

        String maintainCode = queryHash.get("maintainCode");
        if (!StringUtils.isEmpty(maintainCode)) {
            conditionHash.put("(s.orderCode = ?{paramIndex})", maintainCode);
        }
        String searchAudit = queryHash.get("searchAudit");//审批类型
        if (!StringUtils.isEmpty(searchAudit)) {
            if (searchAudit.equals("cancel")) {
                conditionHash.put("(s.cancel = ?{paramIndex}) ", true);
            } else if (searchAudit.equals("delay")) {
                conditionHash.put("(s.delay = ?{paramIndex}) ", true);
            } else if (searchAudit.equals("complete")) {
                conditionHash.put("(s.delay = " + false + " and s.cancel = " + false + " and s.orderStatusEnum = ?{paramIndex}) ", OrderStatusEnum.IN_AUDITING);
            }
        }
        String auditAuthority = queryHash.get("auditAuthority");//审批权限
        if (!StringUtils.isEmpty(auditAuthority)) {
            String userId = auditAuthority.split(",")[0];
            String positionId = auditAuthority.split(",")[1];
            String organizationId = auditAuthority.split(",")[2];
            conditionHash.put("s.organization.id = " + organizationId + " and (s.position.id = " + positionId + " or s.auditSystemUser.id = " + userId + ") and 1= ?{paramIndex} ", 1);
        }

        String auditUserId = queryHash.get("auditUserId");
        String auditPositionId = queryHash.get("auditPositionId");
        if (StringUtils.isNotBlank(auditUserId) && StringUtils.isBlank(auditPositionId)) {
            conditionHash.put("s.auditSystemUser.id = ?{paramIndex}", StringConverters.null2Long(auditUserId));
        } else if (StringUtils.isBlank(auditUserId) && StringUtils.isNotBlank(auditPositionId)) {
            conditionHash.put("s.position.id = ?{paramIndex}", StringConverters.null2Long(auditPositionId));
        } else if (StringUtils.isNotBlank(auditUserId) && StringUtils.isNotBlank(auditPositionId)) {
            conditionHash.put("(s.auditSystemUser.id = ?{paramIndex} or s.position.id = " + auditPositionId + " )", StringConverters.null2Long(auditUserId));
        }

        return conditionHash;
    }

    @Override
    public Long totalRecord(Map<String, String> queryHash) {
        Map<String, Object> conditions = getSearchCondition(queryHash);
        TypedQuery<Long> typedQuery = QueryUtils.getTypedQueryByCondition("select count(s) from MaintainOrder s ", conditions, entityManager, Long.class);
        return typedQuery.getSingleResult();
    }


    /**
     * Dashboard 针对企业统计工单完成率 使 用
     * @param queryHash 参数（时间，企业，状态）
     * @return Long
     */
    public Long totalRecordToDashboard(Map<String, String> queryHash) {
        String searchDate1 = queryHash.get("searchDate1");
        String searchDate2 = queryHash.get("searchDate2");
        String enterpriseId = queryHash.get("enterpriseId");
        String searchOrderStatus = queryHash.get("searchOrderStatus");// 工单状态
        String sql = "select count(*) from maintain_order s WHERE s.enterprise_id = '"+enterpriseId+"' AND s.create_date >= '"+searchDate1+"' AND s.create_date <= '"+searchDate2+"' ";
        if (searchOrderStatus != null) {
            sql+=" AND s.order_status_enum = '"+searchOrderStatus+"' ";
        }
        List resultList = entityManager.createNativeQuery(sql).getResultList();
        return Long.valueOf(resultList.get(0).toString());
    }

    @Override
    public List<MaintainOrder> findList(QueryParam queryParam) {
        String sqlInfo = queryParam.joinJPQL("select s from MaintainOrder s ", "order by s.id desc ");
        Map<String, Object> conditions = getSearchCondition(queryParam.getSqlMap());
        TypedQuery<MaintainOrder> typedQuery = QueryUtils.getTypedQueryByCondition(sqlInfo, conditions, entityManager, MaintainOrder.class);
        return queryParam.findPageList(typedQuery);
    }

    @Override
    public MaintainOrder findModel(Long id) {
        return entityManager.find(MaintainOrder.class, id);
    }

    @Override
    public Integer add(MaintainOrder model) {
        model.setCreateDate(new Date());
        entityManager.persist(model);
        logger.info("MaintainOrderDaoImpl添加maintainOrder成功！");
        return 1;
    }

    @Override
    public Integer update(MaintainOrder model) {
        model.setUpdateDate(new Date());
        entityManager.merge(model);
        return 1;
    }

    @Override
    public Integer delete(Long id) {
        MaintainOrder maintainOrder = entityManager.find(MaintainOrder.class, id);
        entityManager.remove(maintainOrder);
        return 1;
    }

    @Override
    public Integer deleteLogic(MaintainOrder model) {
        model.setDeleteStatus(true);
        return 1;
    }


    public List<MaintainOrder> hqlExcute(String hql, QueryParam queryParam) {
        return queryParam.findPageList(entityManager.createQuery(hql, MaintainOrder.class));
    }


    public List<SystemUser> maintainOrderUser(MaintainOrder maintainOrder, Enterprise enterprise, List<Long> ids) {
        String jpql = "SELECT s FROM SystemUser s WHERE 1 = 1 " +
                " AND " + StringConverters.null2Long(enterprise.getId()) + " in elements(s.enterpriseSet) " +
                "AND s.operationRole = 'common' " +
                "AND s.deleteStatus = false ";

        if (maintainOrder.getAssign()) {
            if (maintainOrder.getOrderAssignTypeEnum() != null && CollectionUtils.isNotEmpty(ids)) {
                if ("USER".equals(maintainOrder.getOrderAssignTypeEnum().getCode())) {
                    jpql += " AND s.id in (" + StringUtils.strip(ids.toString(), "[]") + ") ";
                } else if ("POSITION".equals(maintainOrder.getOrderAssignTypeEnum().getCode())) {
                    jpql += " AND exists (select rs from s.positionSet rs where rs.id in (" + StringUtils.strip(ids.toString(), "[]") + ")) ";
                }
            }
        }
        TypedQuery<SystemUser> resultQuery = this.entityManager.createQuery(jpql, SystemUser.class);
        return resultQuery.getResultList();
    }

    public List<Long> maintainOrderUserIds(MaintainOrder maintainOrder, Enterprise enterprise, List<Long> ids) {
        String jpql = "SELECT s.id FROM SystemUser s WHERE 1 = 1 " +
                " AND " + StringConverters.null2Long(enterprise.getId()) + " in elements(s.enterpriseSet) " +
                "AND s.operationRole = 'common' " +
                "AND s.deleteStatus = false ";

        if (maintainOrder.getAssign()) {
            if (maintainOrder.getOrderAssignTypeEnum() != null && CollectionUtils.isNotEmpty(ids)) {
                if ("USER".equals(maintainOrder.getOrderAssignTypeEnum().getCode())) {
                    jpql += " AND s.id in (" + StringUtils.strip(ids.toString(), "[]") + ") ";
                } else if ("POSITION".equals(maintainOrder.getOrderAssignTypeEnum().getCode())) {
                    jpql += " AND exists (select rs from s.positionSet rs where rs.id in (" + StringUtils.strip(ids.toString(), "[]") + ")) ";
                }
            }
        }
        List<Long> id = this.entityManager.createQuery(jpql).getResultList();
        return id;
    }

    /**
     * appdashboard 根据人员计算接单数
     *
     * @param organizationId
     * @param start
     * @param end
     * @return
     */
    public List<Object> getOrganizationReceiveNum(String organizationId, String start, String end) {
        String sql = "select COUNT(m.id),uo.system_user_id,date_format(receive_date,'%Y-%m-%d') as nowday,Month(m.receive_date) as nowmonth,quarter(m.receive_date)  as nowseason  from  system_user_organization uo  LEFT JOIN maintain_order m  " +
                "on m.receive_user_id=uo.system_user_id and m.delete_status=false and m.receive_date >= '" + start + "' AND m.receive_date < '" + end + "' and m.receive_user_id is not null  WHERE" +
                " uo.organization_id=" + organizationId + " and uo.delete_status=false group by uo.system_user_id";
        sql = sql + " order by uo.system_user_id";
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object> objects = query.getResultList();
        return objects;
    }

    /**
     * Charts 根据人员计算工时
     */
    public List<Object> getHoursChartsData(String startDateTime, String endDateTime, String enterpriseId, String organizationId) {
        String sql = " SELECT SUM(f.working_hours)  FROM " +
                "(SELECT b.id,SUM(a.working_hours) as working_hours FROM maintain_order a " +
                " right JOIN SYSTEM_USER b " +
                " ON a.`receive_user_id` = b.id  AND a.finish_time > '" + startDateTime + "' AND a.finish_time < '" + endDateTime + "' and  a.order_status_enum = 'COMPLETED'  AND a.delete_status = false ";
        if (StringUtils.isNotBlank(enterpriseId)) {
            sql += " AND a.`enterprise_id` = '" + enterpriseId + "' ";
        }
        sql = sql + " LEFT JOIN system_user_organization g " +
                " ON b.id = g.system_user_id " +
                " WHERE b.delete_status=false";

        if (StringUtils.isNotBlank(organizationId)) {
            sql += " AND g.`organization_id` = " + organizationId + " ";
        }
        sql += " GROUP BY b.`id` " +
                " UNION ALL " +
                " SELECT  d.id,SUM(c.working_hours) working_hours FROM maintain_partner c " +
                " LEFT JOIN SYSTEM_USER d " +
                " ON c.partner_id = d.id " +
                " LEFT JOIN maintain_order e " +
                " ON c.maintain_order_id = e.id " +
                " LEFT JOIN system_user_organization h " +
                " ON d.id = h.system_user_id " +
                " WHERE e.order_status_enum = 'COMPLETED'  " +
                " AND c.delete_status = false ";
        if (StringUtils.isNotBlank(enterpriseId)) {
            sql += " AND c.`enterprise_id` = '" + enterpriseId + "' ";
        }
        if (StringUtils.isNotBlank(organizationId)) {
            sql += " AND h.`organization_id` = " + organizationId + " ";
        }
        sql += " AND c.update_date > '" + startDateTime + "' AND c.update_date < '" + endDateTime + "' " +
                " GROUP BY d.`id`) f " +
                " GROUP BY f.id " +
                " ORDER BY f.id ";
        return entityManager.createNativeQuery(sql).getResultList();
    }

    public List<SystemUser> addMaintainUser(MaintainOrder maintainOrder, Enterprise enterprise) {
        List<Long> assignUserIds = new ArrayList<>();
        for (SystemUser systemUser : maintainOrder.getAssignUsers()) {
            assignUserIds.add(systemUser.getId());
        }
        String jpql = "select s from systemUser s Where 1 = 1 " +
                " AND " + StringConverters.null2Long(enterprise.getId()) + " in elements(s.enterpriseSet) " +
                "AND s.operationRole = 'common' " +
                "AND s.deleteStatus = false ";
        if (maintainOrder.getAssign()) {
            if (OrderAssignTypeEnum.USER.getCode().equals(maintainOrder.getOrderAssignTypeEnum().getCode())) {
                jpql += "AND s.id in (" + StringUtils.strip(assignUserIds.toString(), "[]") + ") ";
            } else if (OrderAssignTypeEnum.POSITION.getCode().equals(maintainOrder.getOrderAssignTypeEnum().getCode())) {
                jpql += "AND exists(select asp from s.positionSet asp where asp.id = " + maintainOrder.getPosition().getId() + ") ";
            }
        }
        TypedQuery<SystemUser> resultQuery = this.entityManager.createQuery(jpql, SystemUser.class);
        return resultQuery.getResultList();
    }


    public ResultDto list(String status, String pageIndex, String pageSize, SystemUser systemUser, Enterprise enterprise, String searchData, String date, boolean isLeader, Organization organization) {

        int index = 0;
        int size = 0;
        if (StringUtils.isNotBlank(pageIndex)) {
            index = StringConverters.null2Int(pageIndex);
        }

        if (StringUtils.isNotBlank(pageSize)) {
            size = StringConverters.null2Int(pageSize);
        }

        /** 查询工单hql语句**/
        String hql = "select obj from MaintainOrder obj where obj.deleteStatus is false and obj.enterprise.id=" + enterprise.getId();
        if (!StringConverters.null2String(searchData).equals("")) {
            hql += " and (obj.orderCode like '%" + StringConverters.null2String(searchData) + "%' or obj.orderName like '%" + StringConverters.null2String(searchData) + "%') ";
        }
        if (StringConverters.null2String(status).equals("0")) { //查询未接单的工单
            hql += " and obj.orderStatusEnum = 'WAIT_TAKING' and obj.startDateTime<='" + StringConverters.formatLongDate(new Date()) + "'";
            /********添加指派的工单start******/
            if (isLeader) {
                hql += " and (exists( select asu from obj.assignUsers asu where " + " asu.id = " + systemUser.getId() + " or exists( select suo1 from SystemUserOrganization suo1 where suo1.systemUser.id =asu.id and " +
                        " suo1.organization.parentIds like '%/" + organization.getId() + "/%' ))" +
                        "or exists( select asp from obj.assignPositions asp where exists( select su from asp.systemUserSet su where su.id=" + systemUser.getId() + " or exists( select suo2 from SystemUserOrganization suo2 where suo2.systemUser.id =su.id and " +
                        " suo2.organization.parentIds like '%/" + organization.getId() + "/%' )))";

            } else {
                hql += " and (exists( select asu from obj.assignUsers asu where " + " asu.id = " + systemUser.getId() + ") " +
                        "or exists( select asp from obj.assignPositions asp where exists( select su from asp.systemUserSet su where su.id=" + systemUser.getId() + "))";
            }
            /********添加指派的工单end******/
            /********添加未指派的工单start******/

            hql += " or (not exists(select asu1 from obj.assignUsers asu1 ) and not exists(select asp1 from obj.assignPositions asp1) and " +
                    " exists(select fms from FacilityMaintainSnap fms where fms.areaId is null and fms.maintainOrder.id = obj.id" +
                    " or fms.maintainOrder.id = obj.id and exists(select ar from Area ar " +
                    "where exists(select su1 from ar.systemUserSet su1" +
                    " where su1.id=" + systemUser.getId() + " and ar.id=fms.areaId)) " +
                    ")" +
                    ")" +
                    ")";
            /********添加未指派的工单end******/
        } else if (StringConverters.null2String(status).equals("1")) {//查询已接单的工单
            if (StringUtils.isNotBlank(date)) {
                hql += " and obj.receiveDate <='" + date + "'";
            }
            hql += " and obj.orderStatusEnum in('WAIT_PRESENCE','UNFINISHED') ";
            if (!isLeader) {
                hql += " AND obj.receiveUser.id = " + systemUser.getId() + " ";
            }

            if (isLeader) {
                //接单人不是自己 部门下及下级部门
                hql += " AND (obj.receiveUser.id = " + systemUser.getId() +
                        " OR (obj.receiveUser.id != " + systemUser.getId() + " AND  exists (SELECT rs FROM obj.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))) ";
            }

        } else if (StringConverters.null2String(status).equals("2")) {
            //待审核
            hql += " and (obj.orderStatusEnum = 'IN_AUDITING' or obj.delay is true or obj.cancel is true)";
            if (isLeader) {
                hql += " AND (obj.receiveUser.id = " + systemUser.getId() +//接单人
                        "OR obj.auditSystemUser.id = " + systemUser.getId() + "or exists (select su from SystemUser su where  su.id=" + systemUser.getId() +
                        " and exists(select so from su.systemUserOrganizationSet so where so.organization.id = obj.organization.id) and exists (select pos from su.positionSet pos where pos.id = obj.position.id) )" +//审核人
                        " OR (obj.receiveUser.id != " + systemUser.getId() + " AND  exists (SELECT rs FROM obj.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))) ";//领导
            } else {
                hql += " and ( obj.receiveUser.id = " + systemUser.getId() + " or obj.auditSystemUser.id = " + systemUser.getId() + " or exists( select su from SystemUser su where" +
                        " su.id=" + systemUser.getId() + " and exists( select so from su.systemUserOrganizationSet so where so.organization.id = obj.organization.id) and " +
                        " exists( select pos from su.positionSet pos where pos.id = obj.position.id)))";
            }


        } else if (StringConverters.null2String(status).equals("3")) {
            //已完成
            hql += " and (obj.orderStatusEnum = 'COMPLETED' or obj.orderStatusEnum = 'CANCEL')";
            if (isLeader) {
                hql += " and  (obj.receiveUser.id = " + systemUser.getId() +
                        " or exists(select aud from AuditOrder aud where aud.maintainOrder.id=obj.id and aud.auditUser.id=" + systemUser.getId() + ")" +//与我相关的代码（审核过的）
                        " or (obj.receiveUser.id != " + systemUser.getId() + " and exists(SELECT rs FROM obj.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))) ";//领导
            } else {
                hql += " and ( obj.receiveUser.id = " + systemUser.getId() +
                        " or exists(select aud from AuditOrder aud where aud.maintainOrder.id=obj.id and aud.auditUser.id=" + systemUser.getId() + "))";//与我相关的代码
            }
            // hql+=" or exists(select aud from AuditOrder aud where aud.maintainOrder.id=obj.id and aud.auditUser.id="+systemUser.getId()+")";
        } else if (StringConverters.null2String(status).equals("4")) {
            //批量审核列表
            hql += " and obj.orderStatusEnum = 'IN_AUDITING'";
            hql += " and( obj.auditSystemUser.id = " + systemUser.getId() + " or exists( select su from SystemUser su where" +
                    " su.id=" + systemUser.getId() + " and exists( select so from su.systemUserOrganizationSet so where so.organization.id = obj.organization.id) and " +
                    " exists( select pos from su.positionSet pos where pos.id = obj.position.id)))";
        } else if (StringConverters.null2String(status).equals("5")) {
            //批量转单列表
            hql += " and obj.orderStatusEnum in('WAIT_PRESENCE','UNFINISHED') ";

            if (organization != null ) {
                //接单人不是自己 部门下及下级部门
                hql += " AND (obj.receiveUser.id = " + systemUser.getId() +
                        " OR (obj.receiveUser.id != " + systemUser.getId() + " AND  exists (SELECT rs FROM obj.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))) ";
            } else {
                // 自己
                hql += " and obj.receiveUser.id = " + systemUser.getId();
            }

        }
        hql += " order by obj.id desc";
        JSONObject jsonObject;
        JSONArray jsonArray = new JSONArray();
        TypedQuery<MaintainOrder> resultQuery = this.entityManager.createQuery(hql, MaintainOrder.class);
        logger.info("拼接完成的sql语句为：{}", hql);
        int records = resultQuery.getResultList().size();
        if (index > 0 && size > 0) {
            int minLimit = size * (index - 1);
            int maxLimit = size;
            resultQuery.setFirstResult(minLimit).setMaxResults(maxLimit);
        }

        for (MaintainOrder maintainOrder : resultQuery.getResultList()) {
            jsonObject = new JSONObject();
            jsonObject.put("id", maintainOrder.getId());
            jsonObject.put("maintainOrderName", StringConverters.null2String(maintainOrder.getOrderName()) /*+ StringConverters.formatTime("yyyyMMdd", maintainOrder.getCreateDate())*/);
            jsonObject.put("orderNum", StringConverters.null2String(maintainOrder.getOrderCode()));
            jsonObject.put("startTime", StringConverters.formatTime("yy-MM-dd HH:mm", maintainOrder.getStartDateTime()));
            jsonObject.put("createDate", DateTimeUtils.format(maintainOrder.getStartDateTime(), "MM-dd HH:mm"));
            jsonObject.put("statusCode", maintainOrder.getOrderStatusEnum().getCode());
            jsonObject.put("statusName", maintainOrder.getOrderStatusEnum() == null ? "" : maintainOrder.getOrderStatusEnum().getName());
            jsonObject.put("percent", maintainOrder.getPercent());
            jsonObject.put("receiverName", maintainOrder.getReceiveUser() == null ? "" : maintainOrder.getReceiveUser().getUserName());

            JSONArray tags = new JSONArray();
            JSONObject tag;
            if (maintainOrder.isOrderType()) {
                tag = new JSONObject();
                tag.put("name", "一次性");
                tag.put("color", "blue");
                tags.add(tag);
            } else {
                tag = new JSONObject();
                tag.put("name", "周期性");
                tag.put("color", "blue");
                tags.add(tag);
            }
            if (maintainOrder.getAssign()) {
                tag = new JSONObject();
                tag.put("name", "被指派");
                tag.put("color", "blue");
                tags.add(tag);
            } else  if (null != maintainOrder.getOrderAssignTypeEnum()) {
                tag = new JSONObject();
                tag.put("name", "被指派");
                tag.put("color", "blue");
                tags.add(tag);
            }
//            Set<AuditOrder>auditOrders=maintainOrder.getAuditOrders();
//            if(CollectionUtils.isNotEmpty(auditOrders)){
//                if(!auditOrders.iterator().next().getPass()) {
//                    tag = new JSONObject();
//                    tag.put("name", "驳回");
//                    tag.put("color", "red");
//                    tags.add(tag);
//                }
//            }


            /*****判断是否逾期和异常******/
            if (!maintainOrder.getOrderStatusEnum().getCode().equals("WAIT_PULISHING") && !maintainOrder.getOrderStatusEnum().getCode().equals("WAIT_TAKING")) {
                jsonObject.put("receiverName", maintainOrder.getReceiveUser() == null ? "" : maintainOrder.getReceiveUser().getUserName());//接单人

                if (maintainOrder.getOrderStatusEnum().getCode().equals("WAIT_PRESENCE") || maintainOrder.getOrderStatusEnum().getCode().equals("UNFINISHED")) {
                    if (maintainOrder.getDelayRealTime().before(new Date())) {
                        tag = new JSONObject();
                        tag.put("name", "逾期");
                        tag.put("color", "blue");
                        tags.add(tag);
                    }
                } else if (maintainOrder.getOrderStatusEnum().getCode().equals("IN_AUDITING") || maintainOrder.getOrderStatusEnum().getCode().equals("COMPLETED")) {
                    if (null != maintainOrder.getFinishTime()) {
                        if (maintainOrder.getDelayRealTime().before(maintainOrder.getFinishTime())) {
                            tag = new JSONObject();
                            tag.put("name", "逾期");
                            tag.put("color", "blue");
                            tags.add(tag);
                        }
                    }
                }
                if (maintainOrder.isAbnormal()) {
                    tag = new JSONObject();
                    tag.put("name", "异常");
                    tag.put("color", "red");
                    tags.add(tag);
                }

            }
            if (StringConverters.null2String(status).equals("3")) {
                if (maintainOrder.getReceiveUser().getId().equals(systemUser.getId())) {
                    tag = new JSONObject();
                    tag.put("name", "我的");
                    tag.put("color", "green");
                    tags.add(tag);
                }

                String findSql = "select obj from MaintainOrder obj where obj.deleteStatus is false and obj.id=" + maintainOrder.getId() +
                        " and exists(select aud from AuditOrder aud where aud.maintainOrder.id=obj.id and aud.auditUser.id=" + systemUser.getId() + ")";
                List<MaintainOrder> maintainOrderList = this.entityManager.createQuery(findSql, MaintainOrder.class).getResultList();
                if (CollectionUtils.isNotEmpty(maintainOrderList)) {
                    tag = new JSONObject();
                    tag.put("name", "已审批");
                    tag.put("color", "green");
                    tags.add(tag);
                }
            }
            jsonObject.put("isAuditLast", false);
            if ((StringConverters.null2String(status).equals("4") || StringConverters.null2String(status).equals("2")) && (maintainOrder.getDelay() || maintainOrder.getCancel() || maintainOrder.isAuditing())) {
                if (maintainOrder.isAuditLast()) {
                    jsonObject.put("isAuditLast", true);
                    jsonObject.put("statusName", "审批中");
                    tag = new JSONObject();
                    tag.put("name", "终审中");
                    tag.put("color", "blue");
                    tags.add(tag);
                } else if (maintainOrder.isAuditFirst()) {
                    jsonObject.put("statusName", "审批中");
                    tag = new JSONObject();
                    tag.put("name", "初审中");
                    tag.put("color", "blue");
                    tags.add(tag);
                } else {
                    jsonObject.put("statusName", "审批中");
                    tag = new JSONObject();
                    tag.put("name", "复审中");
                    tag.put("color", "blue");
                    tags.add(tag);
                }
            }
            if (maintainOrder.getDelay()) {
                tag = new JSONObject();
                tag.put("name", "延期");
                tag.put("color", "blue");
                tags.add(tag);
            }
            if (maintainOrder.getCancel()) {
                tag = new JSONObject();
                tag.put("name", "取消");
                tag.put("color", "blue");
                tags.add(tag);
            }


            jsonObject.put("tags", tags);
            jsonArray.add(jsonObject);
        }
        JSONObject result = new JSONObject();
        result.put("rows", jsonArray);
        result.put("records", records);
        return ResultUtil.success(result);
    }


    public List countScore(String score, List<Long> ids) {
        String sql = "SELECT COUNT(*) FROM maintain_order  " +
                "WHERE order_status_enum = 'COMPLETED' and delete_status=false ";
        if (StringUtils.isNotBlank(score)) {
            sql += "AND score in (" + score + ") ";
        }
        if (CollectionUtils.isNotEmpty(ids)) {
            sql += "AND enterprise_id in (" + StringUtils.strip(ids.toString(), "[]") + ") ";
        }
        return entityManager.createNativeQuery(sql).getResultList();
    }

    public List<Object[]> maintainFacilityCount(String enterpriseIds) {
        String sql = "SELECT F.facility_id FROM maintain_order M \n" +
                "LEFT JOIN facility_maintain_snap F\n" +
                "ON M.id = F.maintain_id\n" +
                "WHERE M.delete_status = FALSE AND F.id IS NOT NULL AND F.facility_id IS NOT NULL \n" +
                "AND M.enterprise_id IN (" + enterpriseIds + ") AND M.complete = FALSE AND M.receive_date IS NOT NULL \n" +
                "GROUP BY F.facility_id";
        logger.info("sql语句为：" + sql);
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object[]> queryResultList = query.getResultList();
        return queryResultList;
    }

    public JSONArray getTags(MaintainOrder maintainOrder, SystemUser systemUser, String status) {
        JSONArray tags = new JSONArray();
        JSONObject tag;
        if (maintainOrder.isOrderType()) {
            tag = new JSONObject();
            tag.put("name", "一次性");
            tag.put("color", "blue");
            tags.add(tag);
        } else {
            tag = new JSONObject();
            tag.put("name", "周期性");
            tag.put("color", "blue");
            tags.add(tag);
        }
        if (null != maintainOrder.getOrderAssignTypeEnum()) {
            tag = new JSONObject();
            tag.put("name", "被指派");
            tag.put("color", "blue");
            tags.add(tag);
        }


        /*****判断是否逾期和异常******/
        if (!maintainOrder.getOrderStatusEnum().getCode().equals("WAIT_PULISHING") && !maintainOrder.getOrderStatusEnum().getCode().equals("WAIT_TAKING")) {
            if (maintainOrder.getOrderStatusEnum().getCode().equals("WAIT_PRESENCE") || maintainOrder.getOrderStatusEnum().getCode().equals("UNFINISHED")) {
                if (maintainOrder.getDelayRealTime()!=null&&maintainOrder.getDelayRealTime().before(new Date())) {
                    tag = new JSONObject();
                    tag.put("name", "逾期");
                    tag.put("color", "blue");
                    tags.add(tag);
                }
            } else if (maintainOrder.getOrderStatusEnum().getCode().equals("IN_AUDITING") || maintainOrder.getOrderStatusEnum().getCode().equals("COMPLETED")) {
                if (null != maintainOrder.getFinishTime()) {
                    if (maintainOrder.getDelayRealTime()!=null&&maintainOrder.getDelayRealTime().before(maintainOrder.getFinishTime())) {
                        tag = new JSONObject();
                        tag.put("name", "逾期");
                        tag.put("color", "blue");
                        tags.add(tag);
                    }
                }
            }
            if (maintainOrder.isAbnormal()) {
                tag = new JSONObject();
                tag.put("name", "异常");
                tag.put("color", "red");
                tags.add(tag);
            }

        }
        if (StringConverters.null2String(status).equals("3")) {
            if (maintainOrder.getReceiveUser().getId().equals(systemUser.getId())) {
                tag = new JSONObject();
                tag.put("name", "我的");
                tag.put("color", "green");
                tags.add(tag);
            }

            String findSql = "select obj from MaintainOrder obj where obj.deleteStatus is false and obj.id=" + maintainOrder.getId() +
                    " and exists(select aud from AuditOrder aud where aud.maintainOrder.id=obj.id and aud.auditUser.id=" + systemUser.getId() + ")";
            List<MaintainOrder> maintainOrderList = this.entityManager.createQuery(findSql, MaintainOrder.class).getResultList();
            if (CollectionUtils.isNotEmpty(maintainOrderList)) {
                tag = new JSONObject();
                tag.put("name", "已审批");
                tag.put("color", "green");
                tags.add(tag);
            }
        }
        if ((StringConverters.null2String(status).equals("4") || StringConverters.null2String(status).equals("2")) && (maintainOrder.getDelay() || maintainOrder.getCancel() || maintainOrder.isAuditing())) {
            if (maintainOrder.isAuditLast()) {
                tag = new JSONObject();
                tag.put("name", "终审中");
                tag.put("color", "blue");
                tags.add(tag);
            } else if (maintainOrder.isAuditFirst()) {
                tag = new JSONObject();
                tag.put("name", "初审中");
                tag.put("color", "blue");
                tags.add(tag);
            } else {
                tag = new JSONObject();
                tag.put("name", "复审中");
                tag.put("color", "blue");
                tags.add(tag);
            }
        }
        if (maintainOrder.getDelay()) {
            tag = new JSONObject();
            tag.put("name", "延期");
            tag.put("color", "blue");
            tags.add(tag);
        }
        if (maintainOrder.getCancel()) {
            tag = new JSONObject();
            tag.put("name", "取消");
            tag.put("color", "blue");
            tags.add(tag);
        }
        return tags;
    }

    /**
     * dashboard-获取分区响应时间
     */
    public List<Object> getMaintainOrderResponseTime(Map<String, Object> dataMap) {
        Long enterpriseId = StringConverters.ToLong(dataMap.get("enterpriseId") + "");
        String sql = "SELECT a.id,a.area_name,IFNULL(b.time,0) AS time\n" +
                "FROM(SELECT a.id,a.area_name FROM area a\n" +
                "LEFT JOIN enterprise b\n" +
                "ON a.enterprise_id=b.id\n" +
                "WHERE a.delete_status=FALSE";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += " and b.parent_ids LIKE '%," + enterpriseId + ",%'";
        }
        sql += ")a\n" +
                "LEFT JOIN(SELECT e.id,e.area_name,\n" +
                "ROUND(IFNULL(SUM(TIMESTAMPDIFF(MINUTE,b.create_date,b.receive_date))/COUNT(1),0),0) AS time\n" +
                "FROM facility_maintain a\n" +
                "LEFT JOIN maintain_order b\n" +
                "ON a.maintain_id=b.id\n" +
                "LEFT JOIN facility c\n" +
                "ON a.facility_id=c.id\n" +
                "LEFT JOIN enterprise d\n" +
                "ON b.enterprise_id=d.id\n" +
                "LEFT JOIN area e\n" +
                "ON c.area_id=e.id\n" +
                "WHERE b.create_date IS NOT NULL AND b.receive_date IS NOT NULL\n" +
                "AND e.id IS NOT NULL AND a.delete_status=FALSE AND b.delete_status=FALSE AND c.delete_status=FALSE \n";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += "AND d.parent_ids LIKE '%," + enterpriseId + ",%'\n";
        }
        sql += "GROUP BY e.id)b\n" +
                "ON a.id=b.id";
        sql=sql+" order by a.id ";
        return entityManager.createNativeQuery(sql).getResultList();
    }
}
